package hello;


import java.util.Iterator;
import java.util.List;

import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.Transaction;

import persistence.HibernateUtil;
import auction.chapter14.ItemBidSummary;
import auction.model.Item;
import auction.model.ItemWithListBids;
import auction.model.User;

public class Chapter14HQL
{
	public static void main(String[] args)throws Exception{
		//testQuery();
		//testPaging();
		//testBinding();
		//testQueryHints();
		//testQueryIterate();
		//testPolymorphicQuery();
		//testQueryCollection();
		//testProjectionQuery();
		testJoinQuery();
		//testReportQuery();
	}
	
	public static void testReportQuery()throws Exception{
		Session s=HibernateUtil.getSessionFactory().openSession();
		Transaction tx=s.beginTransaction();
		
		//Query q=s.createQuery("select count(i) from ItemWithListBids i");
		//Query q=s.createQuery("select count(i.title) from ItemWithListBids i");
		//Query q=s.createQuery("select sum(b.price) from BidWithList b");
		
		/* no join */
		//Query q=s.createQuery("select min(b.price),max(b.price) from BidWithList b where b.item.id=1");
		
		/* MySQL pass, MSSQL exception */
		//Query q=s.createQuery("select count(i),i.title from ItemWithListBids i");
		
		//Query q=s.createQuery("select b.item.id,avg(b.price) from BidWithList b group by b.item.id");
		
		//Query q=s.createQuery("select b.item.id,avg(b.price) from BidWithList b group by b.item.id having b.item.id >0");
		
		//Query q=s.createQuery("select new auction.chapter14.ItemBidSummary( count(i),i.title) from ItemWithListBids i group by i.title");
		
		/* correlated subselect: select * from bid where 10 < (select count(*) from item where b.iid=i.iid) */
		//Query q=s.createQuery("from BidWithList b where 10 < (select count(i) from b.item i)");
		
		/* uncorrelated subselect:  */
		//Query q=s.createQuery("from BidWithList b where b.price +1 >= (select max(b2.price) from BidWithList b2)");
		
		/* return items where all bids price less than 100 */
		//Query q=s.createQuery("from ItemWithListBids i where 100 > all (select b.price from i.bids b)");
		
		
		//Query q=s.createQuery("from ItemWithListBids i where 100 <= any (select b.price from i.bids b)");
		
		/* 100 = some, 100 = any,100 in */
		Query q=s.createQuery("from ItemWithListBids i where 100 in (select b.price from i.bids b)");
		
		List list=q.list();
		out("total records:"+list.size());
		if(!list.isEmpty()){
			Object obj=list.get(0);
			if(obj instanceof ItemBidSummary){
				ItemBidSummary ibs=(ItemBidSummary)obj;
				out(ibs.getTitle()+" "+ibs.getCount());
			}
		}
		
		tx.commit();
		s.close();
		
		HibernateUtil.shutdown();
	}
	
	public static void testJoinQuery()throws Exception{
		Session s=HibernateUtil.getSessionFactory().openSession();
		Transaction tx=s.beginTransaction();
		
		//Implicit Join=====
		/* select b.* from bidswithlist,itemwithlist i where b.iid=i.iid and i.title like '%t%' */
		//Query q=s.createQuery("from BidWithList bid where bid.item.title like '%t%'");
		
		/* org.hibernate.QueryException, bids is collection */
		//Query q=s.createQuery("from ItemWithListBids i where i.bids.price > 100");
		
		
		//Join in From clause=====
		/* select i,b from i inner join b on i.iid=b.iid where i.title like 't%' and b.price>10 */
		//Query q=s.createQuery("from ItemWithListBids i join i.bids b where i.title like 't%' and b.price > 10");
		
		//Query q=s.createQuery("from ItemWithListBids i join i.bids b");
		
		/* old EJB QL: don't support now */
		//Query q=s.createQuery("from ItemWithListBids i in(i.bids) b");
		
		/* Outer join,all item showed: select i,b from i left outer join b on i.iid=b.iid and b.price>10 where i.title like '%' */
		//Query q=s.createQuery("from ItemWithListBids i left join i.bids b with b.price > 10 where i.title like '%'");
		
		/* put b.price to where, only item with bid showed: select i,b from i left outer join b on i.iid=b.id where i.title like '%' and b.price>10 */
		//Query q=s.createQuery("from ItemWithListBids i left join i.bids b where i.title like '%' and b.price > 10");
		
		
		//Fetch Join======
		/* eager fetch */
		/* left join fetch i.bids b with show org.hibernate.hql.ast.QuerySyntaxException */
		//Query q=s.createQuery("from ItemWithListBids i left join fetch i.bids b with b.price > 100 where i.title like '%'");
		
		/* book said it's invalid? but it can executed and works */ //TODOs confirm?
		Query q=s.createQuery("from ItemWithListBids i left join fetch i.bids b where b.price > 100");
		
		//Theta-style Join
		/* from Item i,User u where i.seller = u and u.username='abc' same */
		/* from Item i,User u where i.seller.id = u.id and u.username='abc' same */
		/* from Item i join i.seller u where u.username='abc' same */
		
		/* from Item i,Bid b where i.seller=b.bidder */
		
		List list=q.list();
		out("total records:"+list.size());
		if(!list.isEmpty()){
			Object obj=list.get(0);
			if(obj instanceof Object[]){
				Object[] result=(Object[])obj;
				ItemWithListBids i=(ItemWithListBids)result[0];
				out(i.getBids().get(0).getClass().getName());
			}else if(obj instanceof ItemWithListBids){
				ItemWithListBids i=(ItemWithListBids)obj;
				out(" bids in item:"+i.getBids().size());
				out(i.getBids().get(0).getPrice());
			}
		}
		
		tx.commit();
		s.close();
		
		HibernateUtil.shutdown();
	}
	
	public static void testProjectionQuery()throws Exception{
		Session s=HibernateUtil.getSessionFactory().openSession();
		Transaction tx=s.beginTransaction();
		
		Query q=s.createQuery("select title,current_date() from ItemWithListBids");
		
		//Exception:No data type for node: org.hibernate.hql.ast.tree.MethodNode !!!
		//Query q=s.createQuery("select title,current() from ItemWithListBids i");
		List list=q.list();
		out(list.size());
		
		tx.commit();
		s.close();
		
		HibernateUtil.shutdown();
	}
	
	public static void testQueryCollection()throws Exception{
		Session s=HibernateUtil.getSessionFactory().openSession();
		Transaction tx=s.beginTransaction();
		
		//MySQL & MsSQL are same:select * from item where exists(select bid from bids where i.iid=b.iid);
		//Query q=s.createQuery("from Item i where i.bids is not empty");
		//... where not (exists ...)
		Query q=s.createQuery("from Item i where i.bids is empty");
		
		//select * from items where (select count(iid) from bids where iid=iid)>3
		//Query q=s.createQuery("from Item i where size(i.bids)>3");
		
		//min(bids.bid), max(bids.bid)
		//Query q=s.createQuery("from Item i where maxelement(i.bids)>0");
		
		/* select position */
		//Query q=s.createQuery("select index(i.bids) from ItemWithListBids i");
		
		/* select position */
		//Query q=s.createQuery("select indices(i.bids) from ItemWithListBids i");
		
		//max(bid.bid_position),min(bid.bid_position)
		//Query q=s.createQuery("from ItemWithListBids i where minindex(i.bids)>0");
		
		//exists (select bid from bids where iid=iid)
		//Query q=s.createQuery("from Item i where exists elements(i.bids)");
		
		//'2' in (select bid_position from bids where iid=iid)
		//Query q=s.createQuery("from ItemWithListBids i where '2' in indices(i.bids)");
		
		//MySQL & MsSQL are same: select * from bids b,item i where b.bid=1 and b.bid in(select bid from bids where b.itemid=i.itemid)
		//in HQL .id always refer to PK of an entity
		//Query q=s.createQuery("from Bid b, Item i where b.id=1 and b member of i.bids");
		
		//Query q=s.createQuery("from Item i where concat(i.title,i.title,i.title) like 'tt%'");
		List list=q.list();
		out(list.size());
		
		tx.commit();
		s.close();
		
		HibernateUtil.shutdown();
	}
	
	public static void testPolymorphicQuery()throws Exception{
		Session s=HibernateUtil.getSessionFactory().openSession();
		Transaction tx=s.beginTransaction();
		
		Query q=s.createQuery("from "+Object.class.getName());
		List list=q.list();
		out(list.size());
		for(Object obj : list){
			out(obj.getClass().getName());
		}
		
		tx.commit();
		s.close();
		
		HibernateUtil.shutdown();
	}
	
	public static void testQueryIterate()throws Exception{
		Session s=HibernateUtil.getSessionFactory().openSession();
		Transaction tx=s.beginTransaction();
		/*
		Query q=s.createQuery("from Message");
		List<Message> l1=q.list();//select * from Message,bind all values
		
		Query q2=s.createQuery("from Message");
		List<Message> l2=q2.list();//select * from Message,ONLY bind PK
		
		out(l1.get(0) == l2.get(0));//true
		*/
		
		/*
		Query q=s.createQuery("from Item");
		List<Item> l1=q.list();//select * from Item,bind all values
		out(l1.size());
		
		Query q2=s.createQuery("from Item");
		List<Item> l2=q2.list();//select * from Item,ONLY bind PK
		
		out(l1.get(0) == l2.get(0));//true
		*/
		
		Query q=s.createQuery("from Item");//caching Item
		//q.list();
		/*
		Query q2=s.createQuery("from Item");
		Iterator it=q2.iterate();//select PK from item
		Item i=(Item)it.next();//first-level cache hit
		out(i.getTitle());
		*/
		tx.commit();
		s.close();
		
		Session s2=HibernateUtil.getSessionFactory().openSession();
		Transaction tx2=s2.beginTransaction();
		
		Query q2=s2.createQuery("from Item");
		Iterator it=q2.iterate();//select PK from item
		//Hibernate.close(it);//close this iterator,cause No more results Exception
		Item i=(Item)it.next();//second-level cache hit; if cache miss,n+1 problem!
		out(i.getTitle());
		
		tx2.commit();
		s2.close();
		
		HibernateUtil.shutdown();
	}
	
	public static void testQueryHints()throws Exception{
		Session s=HibernateUtil.getSessionFactory().openSession();
		Transaction tx=s.beginTransaction();
		
		Query q=s.createQuery("from Item");
		q.setReadOnly(true);//all obj in read-only mode
		q.setTimeout(1);
		q.setFetchSize(50);//retrieve 50 rows in on batch
		q.setComment("wei comment");
		
		out(q.list().size());
		
		q.setMaxResults(1);
		Object obj=q.uniqueResult();
		out(obj);
		
		tx.commit();
		s.close();
		
		HibernateUtil.shutdown();
	}
	
	public static void testBinding()throws Exception{
		
		Session s1=HibernateUtil.getSessionFactory().openSession();
		Transaction tx1=s1.beginTransaction();
		Item i=new Item("mac air");
		User u=new User("wei");
		i.setSeller(u);
		
		Message m=new Message("aa");
		s1.save(m);
		
		//s1.save(u);
		//s1.save(i);
		
		tx1.commit();
		s1.close();
		
		Session s=HibernateUtil.getSessionFactory().openSession();
		Transaction tx=s.beginTransaction();
		
		Query q=s.createQuery("from Item i where i.title like :search");
		//q.setString("search", "ga%");
		q.setParameter("search", "ga%");//hibernate is smart
		
		//out(q.list());
		
		q=s.createQuery("from Item i where i.seller=:seller");
		//q.setEntity("seller", u);
		//q.setParameter("seller", u);//smart enough
		//q.setParameter("seller", u,Hibernate.entity(User.class));//>org.hibernate.type.ManyToOneType(auction.model.User)
		q.setProperties(i);
		
		//List<Item> l=q.list();
		//out(l.get(0).getId());
		//out(Hibernate.custom(MonetaryAmountUserType.class));
		
		/*
		q=s.createQuery("from Message");
		List l2=q.list();
		Object geti=s.load(Message.class, 1L);
		
		out(geti.getClass().getName());//as query first, obj is in persistence context.even is load it return obj
		out(l2.get(0).getClass().getName());
		out(geti == l2.get(0));//true
		*/
		
		
		Object geti=s.load(Message.class, 1L);
		q=s.createQuery("from Message");
		List l2=q.list();//select * from Message
		
		out(geti.getClass().getName());//load first, this is proxy
		out(l2.get(0).getClass().getName());//to provide obj identity, this is Proxy!
		out(l2.get(1).getClass().getName());//this is obj! smart
		out(geti == l2.get(0));//true
		
		
		tx.commit();
		s.close();
		
		HibernateUtil.shutdown();
	}
	
	public static void testPaging()throws Exception{
		Session s=HibernateUtil.getSessionFactory().openSession();
		Transaction tx=s.beginTransaction();
		
		//depend on dialect, MySQL[limit ?,?] :)
		Query q=s.createQuery("from Item");
		q.setFirstResult(2);
		q.setMaxResults(2);
		
		out(q.list());
		
		tx.commit();
		s.close();
		
		HibernateUtil.shutdown();
	}
	
	public static void testQuery()throws Exception{
		Session s=HibernateUtil.getSessionFactory().openSession();
		Transaction tx=s.beginTransaction();
		
		Item i=new Item("game");
		s.save(i);
		
		tx.commit();
		s.close();
		
		
		
		Session s1=HibernateUtil.getSessionFactory().openSession();
		Transaction tx1=s1.beginTransaction();
		
		Query hqlQuery=s1.createQuery("from Item");
		List<Item> hl=hqlQuery.list();
		out(hl.get(0).getClass().getName());
		
		Query sqlQuery=s1.createSQLQuery("select * from item");//direct execute this sql
		//the array of Object contain each columns
		List<Object[]> sl=sqlQuery.list();
		out(sl.get(0)[0]+" "+ sl.get(0)[1]);
		
		//hibernate knows bind value to Item object, sth like hql
		Query sqlQuery2=s1.createSQLQuery("select {i.*} from item {i}").addEntity("i",Item.class);
		List<Item> sl2=sqlQuery2.list();
		out(sl2.get(0).getClass().getName());
		
		tx1.commit();
		s1.close();
		
		
		
		//HibernateUtil.stat();
		HibernateUtil.shutdown();
	}
	
	public static void getParent(Class clazz)
	{
		Class parent=clazz.getSuperclass();
	  if(parent !=null)
	  {
			System.out.println("###"+parent.getName());
			getParent(parent);
		}
	}
	
	static void out(Object obj)
	{
		System.out.println("--->"+obj);
	}
}
